﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MINERP.MODEL.FactoryTool;
using System.Data.SqlClient;
using System.Data;
using MINERP.MODEL.FoundationModel;
using System.Collections;
using MINERP.COM;

namespace MINERP.DAL.FactoryTool
{
    public class FactoryToolDAL : DALBase
    {
        public FTOrderInfo_Info GetOrderInfo(int inOrderId)
        {

            FTOrderInfo_Info rtnFTOrderInfo = new FTOrderInfo_Info();
            using (SqlConnection connection = new SqlConnection(DALBase.ConnStr))
            {

                string orderInfoSql = "select OrderProductId,OrderProductCode,OrderProductName,ProductId,CtmName,OrderPath,Width,Height,productNum,PageNum,orderinfo.Remark,FetchTime,ShopName from orderinfo,Tb_OrderBag,shop where Tb_OrderBag.OrderBagId=orderinfo.OrderBagId and Tb_OrderBag.shopID=shop.shopID and  OrderProductId=" + inOrderId + " and orderinfo.status=" + (int)OrderInfoStatus.Doing;
                DataTable orderInfoTable = GetDataTableFromSql(connection, orderInfoSql);
                if (orderInfoTable.Rows.Count == 0)
                {
                    return null;//无该产品
                }
                //产品信息
                foreach (DataRow row in orderInfoTable.Rows)
                {
                    FTOrderInfo_Info orderinfo = new FTOrderInfo_Info();
                    orderinfo.OrderProductId = Convert.ToInt32(row["OrderProductId"]);
                    orderinfo.OrderProductName = row["OrderProductName"].ToString();
                    orderinfo.OrderProductCode = row["OrderProductCode"].ToString();
                    orderinfo.ProductId = Convert.ToInt32(row["ProductId"]);
                    orderinfo.CtmName = row["CtmName"].ToString();
                    orderinfo.OrderPath = row["OrderPath"].ToString();
                    orderinfo.Width = Convert.ToDouble(row["Width"]);
                    orderinfo.Height = Convert.ToDouble(row["Height"]);
                    orderinfo.ProductNum = Convert.ToInt32(row["productNum"]);
                    orderinfo.PageNum = Convert.ToInt32(row["PageNum"]);
                    orderinfo.Remark = row["Remark"].ToString();
                    orderinfo.ShopName = row["shopName"].ToString();
                    orderinfo.FetchTime = Convert.ToDateTime(row["FetchTime"]);
                    rtnFTOrderInfo = orderinfo;
                }
                //获取委外和工厂状态
                string orderOutProduct = "select Status,Status2 from OutProcessRecord where OrderProductId=" + inOrderId;
                DataTable orderOutProductTable = GetDataTableFromSql(connection, orderOutProduct);
                int outProductStatus = -1;
                int outProductStatus2 = -1;
                foreach (DataRow row in orderOutProductTable.Rows)
                {
                    outProductStatus = Convert.ToInt32(row["status"]);
                    outProductStatus2 = Convert.ToInt32(row["status2"]);
                }

                rtnFTOrderInfo.intProductStatus = outProductStatus;
                rtnFTOrderInfo.intProductStatus2 = outProductStatus2;
                ///获取照片信息
                List<FSPhotoInfo_Info> photoList = new List<FSPhotoInfo_Info>();
                
                string photoSql = "select photoId,photoName,picPath,Width,Height from photoInfo where OrderProductId=" + rtnFTOrderInfo.OrderProductId;

                DataTable tablePhoto = GetDataTableFromSql(connection,photoSql);
                foreach (DataRow row in tablePhoto.Rows)
                {
                    FSPhotoInfo_Info photo = new FSPhotoInfo_Info();
                    photo.PhotoId = Convert.ToInt32(row["photoId"]);
                    photo.PhotoName = Convert.ToString(row["PhotoName"]);
                    photo.PicPath = row["PicPath"].ToString();
                    photo.Width = Convert.ToDouble(row["Width"]);
                    photo.Height = Convert.ToDouble(row["Height"]);
                    photoList.Add(photo);
                }
                rtnFTOrderInfo.PhotoList = photoList;
                ///获取分单信息
                //DataSet subOrder = new DataSet();
                //SqlCommand cmd_SubOrder = new SqlCommand();
                //cmd_SubOrder.Connection = connection;
                //cmd_SubOrder.CommandText = "select OrderProductID,SubOrderID,CreateTime,CreatUserID,Status,GridID,SubOrderDesDetail from FactorySubOrderInfo,FactorySubOrderDescription where FactorySubOrderInfo.SubOrderDescriptionID=FactorySubOrderDescription.SubOrderDesID and OrderProductID=" + rtnFTOrderInfo.OrderProductId + "order by SubOrderID";
                //cmd_SubOrder.CommandType = CommandType.Text;
                string SubOrderSql = "select OrderProductID,SubOrderID,CreateTime,CreatUserID,Status,GridID,SubOrderDesDetail from FactorySubOrderInfo,FactorySubOrderDescription where FactorySubOrderInfo.SubOrderDescriptionID=FactorySubOrderDescription.SubOrderDesID and OrderProductID=" + rtnFTOrderInfo.OrderProductId + " order by SubOrderID";
                try
                {

                    DataTable tableSubOrder = GetDataTableFromSql(connection, SubOrderSql);
                    //adp_SubOrder.Fill(subOrder, "subOrder");
                    List<FSFactorySubOrderInfo_Info> ListSubOrder = new List<FSFactorySubOrderInfo_Info>();
                    if (tableSubOrder.Rows.Count == 0)
                    {

                    }
                    else
                    {
                        foreach (DataRow row in tableSubOrder.Rows)
                        {
                            FSFactorySubOrderInfo_Info sub = new FSFactorySubOrderInfo_Info();
                            sub.OrderProductID = Convert.ToInt32(row["OrderProductID"]);
                            sub.SubOrderID = Convert.ToInt32(row["SubOrderID"]);
                            sub.CreateTime = Convert.ToDateTime(row["CreateTime"]);
                            sub.Status = Convert.ToInt32(row["Status"]);
                            sub.GridID = Convert.ToInt32(row["GridID"]);
                            sub.Exists = true;
                            sub.SubOrderDesDetail = row["SubOrderDesDetail"].ToString();
                            ListSubOrder.Add(sub);
                        }
                    }
                    rtnFTOrderInfo.FSubOrderList = ListSubOrder;
                }
                catch (Exception exx)
                {
                    throw new Exception(exx.Message);
                }
                finally
                {
                    connection.Close();
                }
            }


            return rtnFTOrderInfo;
        }

        public List<FactorySubOrderDescription_Info> GetAllDes()
        {
            List<FactorySubOrderDescription_Info> rtnList = new List<FactorySubOrderDescription_Info>();
            using (SqlConnection connection = new SqlConnection(DALBase.ConnStr))
            {

                
                string OrderDescriptionSql = "select SubOrderDesID,SubOrderDesDetail from FactorySubOrderDescription ";
                try
                {

                    DataTable tableSubOrderDescription = GetDataTableFromSql(connection,OrderDescriptionSql);
                    foreach (DataRow row in tableSubOrderDescription.Rows)
                    {
                        FactorySubOrderDescription_Info fsod = new FactorySubOrderDescription_Info();
                        fsod.SubOrderDesID = Convert.ToInt32(row["SubOrderDesID"]);
                        fsod.SubOrderDesDetail = row["SubOrderDesDetail"].ToString();
                        rtnList.Add(fsod);
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }

            }
            return rtnList;
        }

        public int DeleteSubOrder(FSFactorySubOrderInfo_Info inInfo, out List<FSFactorySubOrderInfo_Info> outList)
        {
            int rtnInt = 0;
             List<FSFactorySubOrderInfo_Info> ListSubOrder = new List<FSFactorySubOrderInfo_Info>();
            using (SqlConnection connection = new SqlConnection(DALBase.ConnStr))
            {
                
                    if (connection.State != ConnectionState.Open)
                    {
                        connection.Open();
                    }
                    SqlTransaction strTransaction = connection.BeginTransaction();
                    try
                    {
                        //删除分单
                        string delSql = "delete from FactorySubOrderInfo where OrderProductID= " + inInfo.OrderProductID + " and SubOrderID=" + inInfo.SubOrderID;
                        try
                        {

                            rtnInt = ExeSql(connection, delSql, strTransaction);
                        }
                        catch (Exception exx)
                        {
                            strTransaction.Rollback();
                            outList = new List<FSFactorySubOrderInfo_Info>();
                            return 0;
                        }


                        DataTable subOrder = new DataTable();

                        string SelectSubOrder = "select OrderProductID,SubOrderID,CreateTime,CreatUserID,Status,GridID,SubOrderDesDetail from FactorySubOrderInfo,FactorySubOrderDescription where FactorySubOrderInfo.SubOrderDescriptionID=FactorySubOrderDescription.SubOrderDesID and OrderProductID=" + inInfo.OrderProductID + " order by SubOrderID";
                        try
                        {
                            subOrder = GetDataTableFromSql(connection, SelectSubOrder, strTransaction);
                            if (subOrder.Rows.Count == 0)
                            {
                                outList = new List<FSFactorySubOrderInfo_Info>();
                                strTransaction.Rollback();
                                return 0;
                            }
                            else
                            {
                                int orderInt = 1;
                                bool isOrder = true;
                                foreach (DataRow row in subOrder.Rows)
                                {
                                    FSFactorySubOrderInfo_Info sub = new FSFactorySubOrderInfo_Info();
                                    sub.OrderProductID = Convert.ToInt32(row["OrderProductID"]);
                                    sub.SubOrderID = Convert.ToInt32(row["SubOrderID"]);
                                    sub.CreateTime = Convert.ToDateTime(row["CreateTime"]);
                                    sub.Status = Convert.ToInt32(row["Status"]);
                                    sub.GridID = Convert.ToInt32(row["GridID"]);
                                    sub.Exists = true;
                                    sub.SubOrderDesDetail = row["SubOrderDesDetail"].ToString();
                                    if (orderInt != sub.GridID)//检查顺序
                                    {
                                        isOrder = false;

                                    }
                                    orderInt++;
                                    ListSubOrder.Add(sub);
                                }
                                if (!isOrder)//调整顺序
                                {
                                    ArrayList sqlList = new ArrayList();
                                    int reOrder = 1;
                                    List<FSFactorySubOrderInfo_Info> ListReOrder = new List<FSFactorySubOrderInfo_Info>();
                                    foreach (FSFactorySubOrderInfo_Info fsinfo in ListSubOrder)
                                    {
                                        string sql = "update FactorySubOrderInfo set SubOrderID=" + reOrder + " where OrderProductID=" + inInfo.OrderProductID + " and SubOrderID=" + fsinfo.SubOrderID;
                                        sqlList.Add(sql);

                                        fsinfo.SubOrderID = reOrder;
                                        ListReOrder.Add(fsinfo);
                                        reOrder++;
                                    }
                                    outList = ListReOrder;
                                    //return 2;//调整顺序
                                    try
                                    {
                                        ExecuteSqlTran(sqlList, connection, strTransaction);
                                    }
                                    catch (Exception ex)
                                    {

                                    }

                                }
                                else
                                {
                                    //正常出可以return
                                }

                            }
                        }
                        catch (Exception exx)
                        {
                            throw new Exception(exx.Message);
                        }
                        
                        strTransaction.Commit();
                    }
                    catch (Exception exx)
                    {
                        strTransaction.Rollback();
                    }
                    finally
                    {
                        if (connection.State != ConnectionState.Closed)
                        {
                            connection.Close();
                        }
                    }
            }
            outList = ListSubOrder;
            return rtnInt;
        }
        public void ExecuteSqlTran(ArrayList SQLStringList, SqlConnection conn,SqlTransaction tx)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
               
            }
            catch (System.Data.SqlClient.SqlException E)
            {
              
                throw new Exception(E.Message);
            }
        }

        public int InsertSubOrder(FTOrderInfo_Info InOrder)
        {
            ArrayList SqlInsertList = new ArrayList();
            List<FSFactorySubOrderInfo_Info> inSubList=InOrder.FSubOrderList;
            List<FactorySubOrderDescription_Info> FSDesList = GetAllDes();
            using (SqlConnection connection = new SqlConnection(DALBase.ConnStr))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
               SqlTransaction  strTransaction=connection.BeginTransaction();
               try
               {
                   foreach (FSFactorySubOrderInfo_Info info in inSubList)
                   {
                       //ArrayList SQLDesList = new ArrayList();

                       if (!info.Exists)
                       {
                           string strSubOrderDesDetail = info.SubOrderDesDetail.Trim();
                           FactorySubOrderDescription_Info fsodi = FSDesList.FirstOrDefault(p => p.SubOrderDesDetail == strSubOrderDesDetail);
                           if (fsodi != null)
                           {
                               info.SubOrderDescriptionID = fsodi.SubOrderDesID;
                           }
                           else
                           {
                               string strInSql = "insert into FactorySubOrderDescription (SubOrderDesDetail) values ('" + info.SubOrderDesDetail + "')";
                               int rtnInt = ExeSql(connection, strInSql, strTransaction);
                               if (rtnInt > 0)
                               {
                                   FSDesList = new List<FactorySubOrderDescription_Info>();
                                   string strSelect = "select SubOrderDesID,SubOrderDesDetail from FactorySubOrderDescription";
                                   DataTable table = GetDataTableFromSql(connection, strSelect, strTransaction);
                                   foreach (DataRow row in table.Rows)
                                   {
                                       FactorySubOrderDescription_Info fsod = new FactorySubOrderDescription_Info();
                                       fsod.SubOrderDesID = Convert.ToInt32(row["SubOrderDesID"]);
                                       fsod.SubOrderDesDetail = row["SubOrderDesDetail"].ToString();
                                       FSDesList.Add(fsod);
                                   }
                                   FactorySubOrderDescription_Info fsodi1 = FSDesList.FirstOrDefault(p => p.SubOrderDesDetail == strSubOrderDesDetail);
                                   info.SubOrderDescriptionID = fsodi1.SubOrderDesID;
                               }
                           }

                           string SubOrderSqlInsert = "insert into FactorySubOrderInfo values (" + info.OrderProductID + "," + info.SubOrderID + "," + info.SubOrderDescriptionID + ",'" + DateTime.Now + "'," + info.CreatUserID + "," + (int)FactoryGridStatus.FactoryDoing + "," + "0" + ")";
                           int rtnSubOrderSqlInsert = ExeSql(connection, SubOrderSqlInsert, strTransaction);
                           if (rtnSubOrderSqlInsert == 0)
                           {
                               strTransaction.Rollback();
                               return 0;
                           }
                       }
                       else
                       {
                           string updateStrSql = "update FactorySubOrderInfo set Status=" + (int)FactoryGridStatus.FactoryDoing + ", GridID=0 where OrderProductID=" + info.OrderProductID + " and SubOrderID=" + info.SubOrderID;
                           int rtnSubOrderSqlUpdate = ExeSql(connection, updateStrSql, strTransaction);
                           if (rtnSubOrderSqlUpdate == 0)
                           {
                               strTransaction.Rollback();
                               return 0;
                           }
                       }

                       //if (info.Exists)
                       //{
                       //    //string strSql="update FactorySubOrderInfo set SubOrderID="+info.SubOrderID+", "
                       //}
                       //else
                       //{
                       //    string dota=",";
                       //    string strSql="insert into FactorySubOrderInfo values("+info.OrderProductID+dota+info.SubOrderID+dota+")"
                       //}
                   }
                   if (InOrder.intProductStatus == (int)OutProcessRecordStatus.Outing && InOrder.intProductStatus2 == (int)OutProcessRecordStatus2.Wait)//修改委外记录
                   {

                       string updateOutProductStrSql = "update OutProcessRecord set Status2=" + (int)OutProcessRecordStatus2.Doing + ",StartProductTime='" + DateTime.Now.ToString() + "',EndProductTime='"+"2000-01-01"+"' where OrderProductId=" + InOrder.OrderProductId + " and OutProcessRecord.Status2=" + (int)OutProcessRecordStatus2.Wait;
                       try
                       {
                           int rtnOutProductSqlUpdate = ExeSql(connection, updateOutProductStrSql, strTransaction);
                       }
                       catch (Exception ex)
                       {
                           strTransaction.Rollback();
                       }
                   }
                   strTransaction.Commit();
               }
               catch (Exception ex)
               {
                   throw ex;
               }
               finally
               {
                   if (connection.State != ConnectionState.Closed)
                   {
                       connection.Close();
                   }
               }
            }
            return 1;
        }
        private DataTable GetDataTableFromSql(SqlConnection conn, string strSql)
        {
            DataSet set = new DataSet();
            DataTable rtnTable = new DataTable();
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSql;
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(set, "table");
                rtnTable = set.Tables[0];

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return rtnTable;
        }
        private DataTable GetDataTableFromSql(SqlConnection conn, string strSql,SqlTransaction tx)
        {
            DataSet set = new DataSet();
            DataTable rtnTable = new DataTable();
            if (conn.State != ConnectionState.Open)
                conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSql;
            cmd.Transaction = tx;
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(set, "table");
                rtnTable = set.Tables[0];

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return rtnTable;
        }
        private int ExeSql(SqlConnection conn, string strSql)
        {
            int rtnInt = 0;
            try
            {
                DataSet set = new DataSet();
                DataTable rtnTable = new DataTable();
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;
                 rtnInt = cmd.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            return rtnInt;
        }
        private int ExeSql(SqlConnection conn, string strSql ,SqlTransaction tx)
        {
            int rtnInt = 0;
            try
            {
                DataSet set = new DataSet();
                DataTable rtnTable = new DataTable();
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;
                cmd.Transaction = tx;
                rtnInt = cmd.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            return rtnInt;
        }

    }
}
